#coding=utf-8
# -*- coding: UTF-8 -*-

import pymysql

#将参数导入数据库
def insert_into_database(params):
    # 打开数据库连接
    print("*********************ready to insert params into database*************************")
    db = pymysql.connect(host='47.107.250.201', user='python', passwd='python123456', db='model', charset='utf8')
    print("----------database successfully connected-----------------------")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # 获取参数
    print("----------extracting parameters-----------------------")
    p_application_id = params.get('application_id')
    p_contract_id = params.get('contract_id')
    p_collateral = params.get('collateral')
    p_duration = params.get('duration')
    p_debt_amount = params.get('debt_amount')
    p_interest_rate = params.get('interest_rate')
    p_client_name = params.get('client_name')
    p_gender = params.get('gender')
    p_age = params.get('age')
    p_marriage = params.get('marriage')
    p_id_number = params.get('id_number')
    p_education = params.get('education')
    p_job = params.get('job')
    p_salary = params.get('salary')
    p_housing = params.get('housing')
    p_car = params.get('car')
    p_current_address_length = params.get('current_address_length')
    p_current_job_length = params.get('current_job_length')
    p_loan_approv_1m = params.get('loan_approv_1m')
    p_credit_card_1m = params.get('credit_card_1m')
    p_post_loan_1m = params.get('post_loan_1m')
    p_personal_check_1m = params.get('personal_check_1m')
    p_loan_approv_1y = params.get('loan_approv_1y')
    p_credit_card_1y = params.get('credit_card_1y')
    p_post_loan_1y = params.get('post_loan_1y')
    p_personal_check_1y = params.get('personal_check_1y')
    p_num_otsd_loan = params.get('num_otsd_loan')
    p_num_unliquidated_agency = params.get('num_unliquidated_agency')
    p_amount_otsd_loan = params.get('amount_otsd_loan')
    p_balance_otsd_loan = params.get('balance_otsd_loan')
    p_loan_overdue_2y = params.get('loan_overdue_2y')
    p_total_month_overdue_2y = params.get('total_month_overdue_2y')
    p_longest_month_overdue_2y = params.get('longest_month_overdue_2y')
    p_loan_overdue_5y = params.get('loan_overdue_5y')
    p_total_month_overdue_5y = params.get('total_month_overdue_5y')
    p_longest_month_overdue_5y = params.get('longest_month_overdue_5y')
    p_num_uncancelled_account = params.get('num_uncancelled_account')
    p_num_uncancelled_card_agency = params.get('num_uncancelled_card_agency')
    p_total_credit_granted = params.get('total_credit_granted')
    p_total_credit_used = params.get('total_credit_used')
    p_overdue_card_account_5y = params.get('overdue_card_account_5y')
    p_total_overdue_card_month_5y = params.get('total_overdue_card_month_5y')
    p_longest_overdue_card_month_5y = params.get('longest_overdue_card_month_5y')
    p_overdue_card_account_2y = params.get('overdue_card_account_2y')
    p_total_overdue_card_month_2y = params.get('total_overdue_card_month_2y')
    p_longest_overdue_card_month_2y = params.get('longest_overdue_card_month_2y')
    #若application_id 已存在，则在数据库中删除
    cursor.execute("DELETE FROM CINFO WHERE application_id='%s'"%(p_application_id))
    db.commit()
    # SQL 插入语句
    sql="INSERT INTO CINFO(application_id, contract_id, collateral, duration, debt_amount, interest_rate, client_name, gender, age, marriage, id_number, education, job, salary, housing, car, current_address_length, current_job_length, loan_approv_1m, credit_card_1m, post_loan_1m, personal_check_1m, loan_approv_1y, credit_card_1y, post_loan_1y, personal_check_1y, num_otsd_loan, num_unliquidated_agency, amount_otsd_loan, balance_otsd_loan, loan_overdue_2y, total_month_overdue_2y, longest_month_overdue_2y, loan_overdue_5y, total_month_overdue_5y, longest_month_overdue_5y, num_uncancelled_account, num_uncancelled_card_agency, total_credit_granted, total_credit_used, overdue_card_account_5y, total_overdue_card_month_5y, longest_overdue_card_month_5y, overdue_card_account_2y, total_overdue_card_month_2y, longest_overdue_card_month_2y) \
     VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    # 执行sql语句
    cursor.execute(sql,(p_application_id, p_contract_id, p_collateral, p_duration, p_debt_amount, p_interest_rate, p_client_name, p_gender, p_age, p_marriage, p_id_number, p_education, p_job, p_salary, p_housing, p_car, p_current_address_length, p_current_job_length, p_loan_approv_1m, p_credit_card_1m, p_post_loan_1m, p_personal_check_1m, p_loan_approv_1y, p_credit_card_1y, p_post_loan_1y, p_personal_check_1y, p_num_otsd_loan, p_num_unliquidated_agency, p_amount_otsd_loan, p_balance_otsd_loan, p_loan_overdue_2y, p_total_month_overdue_2y, p_longest_month_overdue_2y, p_loan_overdue_5y, p_total_month_overdue_5y, p_longest_month_overdue_5y, p_num_uncancelled_account, p_num_uncancelled_card_agency, p_total_credit_granted, p_total_credit_used, p_overdue_card_account_5y, p_total_overdue_card_month_5y, p_longest_overdue_card_month_5y, p_overdue_card_account_2y, p_total_overdue_card_month_2y, p_longest_overdue_card_month_2y))
    # 提交到数据库执行
    db.commit()
    print("-----------------parameters successfully inserted into database--------------------------")
    # 关闭数据库连接
    cursor.close()
    db.close()
    return

#将分数导入数据库
def score_to_database(params, pd, score):
    # 打开数据库连接
    print("********************ready to insert credit score into database*****************************")
    db = pymysql.connect(host='47.107.250.201', user='python', passwd='python123456', db='model', charset='utf8')
    print("----------database successfully connected-----------------------")
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # 获取参数
    print("----------extracting parameters-----------------------")
    p_application_id=params.get('application_id')
    p_ret_code=1;
    p_ret_msg='success';
    #若application_id 已存在，则在数据库中删除
    cursor.execute("DELETE FROM CSCORE WHERE application_id='%s'"%(p_application_id))
    db.commit()
    # SQL 插入语句
    sql="INSERT INTO CSCORE(application_id, ret_code, ret_msg, pd, score) \
    VALUES (%s, %s, %s, %s, %s)"
   # 执行sql语句
    cursor.execute(sql,(p_application_id, p_ret_code, p_ret_msg, pd, score))
    # 提交到数据库执行
    db.commit()
    print("-----------------score successfully inserted into database--------------------------")
    # 关闭数据库连接
    cursor.close()
    db.close()
    #生成返回值字典
    score_dict={'ret_code': p_ret_code, 'ret_msg':p_ret_msg, 'application_id':p_application_id,'score':score, 'pd':pd}
    return score_dict

